package com.goa.utils;

import com.goa.entity.DeviceResult;

import java.sql.*;
import java.util.List;

/**
 * JDBC工具类
 */
public class JdbcUtil {  
  
    // 数据库URL、用户名和密码应该根据实际情况配置  
    private static final String DB_URL = "jdbc:mysql://localhost:3306/iot-gateway?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "root";
  
    // 获取数据库连接  
    public static Connection getConnection() throws SQLException {  
        return DriverManager.getConnection(DB_URL, USER, PASS);  
    }

    // 执行DDL语句
    public static void executeDDL(String sql) throws SQLException {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.execute(sql); // 对于DDL语句，我们使用Statement的execute方法
            System.out.println("Table  created successfully.");
        } finally {
            // 在finally块中关闭资源
            closeResources(stmt, conn);
        }
    }

    // 执行查询并返回结果集（需要手动关闭结果集和语句）  
    public static ResultSet executeQuery(String sql, Object... params) throws SQLException {  
        Connection conn = null;  
        PreparedStatement stmt = null;  
        ResultSet rs = null;  
  
        try {  
            conn = getConnection();  
            stmt = conn.prepareStatement(sql);  
            if (params != null) {  
                for (int i = 0; i < params.length; i++) {  
                    stmt.setObject(i + 1, params[i]);  
                }  
            }  
            rs = stmt.executeQuery();  
            return rs; // 注意：调用者需要负责关闭结果集、语句和连接  
        } catch (SQLException e) {  
            // 可以在这里添加日志记录  
            throw e;  
        }  
    }  
  
    // 执行更新操作（插入、更新、删除）  
    public static int executeUpdate(String sql, Object... params) throws SQLException {  
        Connection conn = null;  
        PreparedStatement stmt = null;  
        int affectedRows = 0;  
  
        try {  
            conn = getConnection();  
            stmt = conn.prepareStatement(sql);  
            if (params != null) {  
                for (int i = 0; i < params.length; i++) {  
                    stmt.setObject(i + 1, params[i]);  
                }  
            }  
            affectedRows = stmt.executeUpdate();  
            return affectedRows; // 调用者通常不需要关心资源关闭，但这里为了完整性还是提供了关闭方法  
        } finally {  
            // 在finally块中关闭资源  
            closeResources(stmt, conn);  
        }  
    }  
  
    // 关闭资源（语句和连接），如果它们不为null  
    private static void closeResources(AutoCloseable... resources) {  
        for (AutoCloseable resource : resources) {  
            if (resource != null) {  
                try {  
                    resource.close();  
                } catch (Exception e) {  
                    // 可以在这里添加日志记录，但通常不建议在关闭资源时抛出异常  
                }  
            }  
        }  
    }

    /**
     * 构建DDL语句
     * @param tableName 表名称
     * @param deviceResultList  映射表集合 获取到DDL相关信息
     * @return
     */
    public static String appendSql(String tableName, List<DeviceResult> deviceResultList){
        // 2. 构建SQL语句
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        sql.append(tableName).append(" (");
        // 添加默认的主键ID字段
        sql.append("id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键', ");
        // 添加默认的时间戳字段
//        sql.append("created_at VARCHAR(20) AS (DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')), ");
        sql.append("device_id VARCHAR(255)  DEFAULT NULL COMMENT '设备id', ");
        //添加自定义数据列
        for (DeviceResult deviceResult : deviceResultList) {
            sql.append(deviceResult.getResultColumn()+" "+deviceResult.getColumnType()).
                    append(" DEFAULT NULL").append(" COMMENT"+' '+"'"+deviceResult.getResultName()+"'").
                    append(", ");
        }
        sql.append("timestamp bigint COMMENT '时间戳', ");
        sql.append("created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', ");
        // 移除最后一个逗号并添加闭合括号
        sql.setLength(sql.length() - 2); // 移除最后一个逗号和空格
        sql.append(")");
        return sql.toString();
    }
/*
    *//**
     * 测试DDL语句创建
     * @param args
     *//*
    public static void main(String[] args) {
        *//*
        *******************************示例如下**********************************
        * CREATE TABLE data_123 (id INT AUTO_INCREMENT PRIMARY KEY,
        * timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        *  A_current INT DEFAULT NULL COMMENT 'A相电流有效值',
        * B_current INT DEFAULT NULL COMMENT 'B相电流有效值',
        * C_current INT DEFAULT NULL COMMENT 'C相电流有效值',
        * A_voltage INT DEFAULT NULL COMMENT 'A相电压有效值',
        * B_voltage INT DEFAULT NULL COMMENT 'B相电压有效值',
        *  C_voltage INT DEFAULT NULL COMMENT 'C相电压有效值')
        ************************************************************************
        * *//*
        List<DeviceResult> deviceResultList = new ArrayList<>();
        // 添加数据到集合中
        deviceResultList.add(new DeviceResult(1, 1, 4, 5, "A相电流有效值", "A_current", "INT",1));
        deviceResultList.add(new DeviceResult(2, 1, 6, 7, "B相电流有效值", "B_current", "INT",2));
        deviceResultList.add(new DeviceResult(3, 1, 8, 9, "C相电流有效值", "C_current", "INT", 3));
        deviceResultList.add(new DeviceResult(4, 1, 10, 11, "A相电压有效值", "A_voltage", "INT", 4));
        deviceResultList.add(new DeviceResult(5, 1, 12, 13, "B相电压有效值", "B_voltage", "INT", 5));
        deviceResultList.add(new DeviceResult(6, 1, 14, 15, "C相电压有效值", "C_voltage", "INT", 6));
        String data_123 = appendSql("data_123", deviceResultList);
        System.out.println("data_123 = " + data_123);
    }*/
}